package org.power.core.util;

import java.io.*;
import java.util.*;

import jxl.Sheet;
import jxl.Workbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//import org.junit.jupiter.api.Test;
import org.springframework.lang.NonNull;
import org.springframework.lang.Nullable;

public class XlsxUtil {

    /*public static void main(String[] args) throws IOException {
        String name = "43 12号主变35KV套管升高座C相";
        String[] names = name.split(" ");
        System.out.println(names[1]);
        System.out.println(name.substring(name.indexOf(" ") + 1, name.indexOf("号") + 1));
        InputStream inputStream = XlsxUtil.class.getClassLoader().getResourceAsStream("D:/Program/MyJava/MyIdea/PowerTransformerFault/target/classes/static/data/pre/before/true.xlsx");
    }*/

    public static void test() throws IOException {
        String path = "D:\\Program\\MyJava\\MyIdea\\PowerTransformerFault\\src\\main\\resources\\static\\data\\one.xlsx";
        File file = new File(path);
        FileInputStream is = new FileInputStream(file);
        XSSFWorkbook excel = new XSSFWorkbook(is);
        XSSFSheet sheet = excel.getSheetAt(0);

        Iterator rowIterator = sheet.iterator();
        Iterator cellIterator;
        XSSFRow row;
        double value = 0;
        String type;
        while (rowIterator.hasNext()) {
            row = (XSSFRow) rowIterator.next();
            cellIterator = row.cellIterator();
            XSSFCell cell;
            while (cellIterator.hasNext()) {
                cell = (XSSFCell) cellIterator.next();
                type = cell.getCellType().toString();
                if (!type.equalsIgnoreCase("BLANK")) {
                    if (type.equalsIgnoreCase("String")) {
                        System.out.print(cell.getStringCellValue() + "  ");
                    } else if (type.equalsIgnoreCase("NUMERIC")) {
                        System.out.print(cell.getNumericCellValue() + "  ");
                    } else {
                        break;
                    }
                }
            }
            System.out.println();
        }
        excel.close();
        is.close();
    }

    public static List getOne(@NonNull String path) throws IOException {
        InputStream inputStream = new FileInputStream(path);
        XSSFWorkbook excel = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = excel.getSheetAt(0);
        System.out.println("LastRowNum=>" + excel.getSheetAt(0).getLastRowNum());

        int equipmentTemperatureIndex = 2;
        int environmentTemperatureIndex = 10;
        int environmentHumidityIndex = 11;
        int windSpeedIndex = 12;
        int timeIndex = 5;
        int windDirectionIndex = 13;
        int equipmentNameIndex = 1;
        int holderIndex = 17;

        List timeList = new ArrayList();
        List equipmentTemperatureList = new ArrayList();
        List environmentTemperatureList = new ArrayList();
        List environmentHumidityList = new ArrayList();
        List windSpeedList = new ArrayList();
        List windDirectionList = new ArrayList();
        List equipmentNameList = new ArrayList();
        List holderList = new ArrayList();

        Iterator rowIterator = sheet.iterator();
        //第一行是标签,不需要
        rowIterator.next();
        XSSFRow row;
        int count = 0;
        String timeValue;
        String time;
        String value;
        while (rowIterator.hasNext()) {
            row = (XSSFRow) rowIterator.next();
            XSSFCell timeCell = row.getCell(timeIndex);
            timeValue = timeCell.getStringCellValue();
            time = timeValue.substring(timeValue.indexOf(" ") + 1);
            count++;
            timeList.add(time);

            if (!row.getCell(equipmentTemperatureIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                equipmentTemperatureList.add(row.getCell(equipmentTemperatureIndex).getStringCellValue());
            } else {
                equipmentTemperatureList.add("0");
            }

            if (!row.getCell(environmentTemperatureIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                environmentTemperatureList.add(row.getCell(environmentTemperatureIndex).getStringCellValue());
            } else {
                environmentTemperatureList.add("0");
            }

            if (!row.getCell(environmentHumidityIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                environmentHumidityList.add(row.getCell(environmentHumidityIndex).getStringCellValue());
            } else {
                environmentHumidityList.add("0");
            }

            if (!row.getCell(windSpeedIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                windSpeedList.add(row.getCell(windSpeedIndex).getStringCellValue());
            } else {
                windSpeedList.add("0");
            }

            if (!row.getCell(windDirectionIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                windDirectionList.add(row.getCell(windDirectionIndex).getStringCellValue());
            } else {
                windDirectionList.add("空");
            }

            if (!row.getCell(equipmentNameIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                value = row.getCell(equipmentNameIndex).getStringCellValue();
                value = value.substring(7);
                equipmentNameList.add(value);
            } else {
                equipmentNameList.add("空");
            }

            if (!row.getCell(holderIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                holderList.add(row.getCell(holderIndex).getStringCellValue());
            } else {
                holderList.add("空");
            }
        }

        excel.close();
        inputStream.close();
        List list = new ArrayList();
        list.add(timeList);
        list.add(equipmentTemperatureList);
        list.add(environmentTemperatureList);
        list.add(environmentHumidityList);
        list.add(windSpeedList);
        list.add(windDirectionList);
        list.add(equipmentNameList);
        list.add(holderList);

        list.add(getEquipmentInformation(sheet.getRow(2)));
        System.out.println("count=>" + count);
        return list;
    }

    public static List getAllData(@Nullable String path, String deviceTime, String deviceName) throws IOException {
        File file = new File(path);
        FileInputStream is = new FileInputStream(file);
        XSSFWorkbook excel = new XSSFWorkbook(is);
        XSSFSheet sheet = excel.getSheetAt(0);
        System.out.println("LastRowNum=>" + excel.getSheetAt(0).getLastRowNum());
        int equipmentTemperatureIndex = getRow(path, "设备温度(℃)");
        int environmentTemperatureIndex = getRow(path, "环境温度(℃)");
        int environmentHumidityIndex = getRow(path, "环境湿度");
        int windSpeedIndex = getRow(path, "风速(m/s)");
        int timeIndex = getRow(path, "检测时间");
        int windDirectionIndex = getRow(path, "风向");
        int equipmentNameIndex = 1;
        int holderIndex = getRow(path, "云台方位");

        List timeList = new ArrayList();
        List equipmentTemperatureList = new ArrayList();
        List environmentTemperatureList = new ArrayList();
        List environmentHumidityList = new ArrayList();
        List windSpeedList = new ArrayList();
        List windDirectionList = new ArrayList();
        List equipmentNameList = new ArrayList();
        List holderList = new ArrayList();

        Iterator rowIterator = sheet.iterator();
        rowIterator.next();
        rowIterator.next();
        XSSFRow row;
        int count = 0;
        String timeValue;
        String day;
        String time;
        String value;
        while (rowIterator.hasNext()) {
            row = (XSSFRow) rowIterator.next();
            //count++;
            //System.out.println(count);
            XSSFCell timeCell = row.getCell(timeIndex);
            timeValue = timeCell.getStringCellValue();
            day = timeValue.substring(0, timeValue.indexOf(" "));
            time = timeValue.substring(timeValue.indexOf(" ") + 1);
            //XSSFCell cell;//&&deviceCell.getStringCellValue().equals(deviceName)
            if (day.equals(deviceTime)) {
                //System.out.println("timeValue=>"+timeValue);
                count++;
                timeList.add(time);

                if (!row.getCell(equipmentTemperatureIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                    equipmentTemperatureList.add(row.getCell(equipmentTemperatureIndex).getStringCellValue());
                } else {
                    equipmentTemperatureList.add("0");
                }

                if (!row.getCell(environmentTemperatureIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                    environmentTemperatureList.add(row.getCell(environmentTemperatureIndex).getStringCellValue());
                } else {
                    environmentTemperatureList.add("0");
                }

                if (!row.getCell(environmentHumidityIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                    environmentHumidityList.add(row.getCell(environmentHumidityIndex).getStringCellValue());
                } else {
                    environmentHumidityList.add("0");
                }

                if (!row.getCell(windSpeedIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                    windSpeedList.add(row.getCell(windSpeedIndex).getStringCellValue());
                } else {
                    windSpeedList.add("0");
                }

                if (!row.getCell(windDirectionIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                    windDirectionList.add(row.getCell(windDirectionIndex).getStringCellValue());
                } else {
                    windDirectionList.add("空");
                }

                if (!row.getCell(equipmentNameIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                    value = row.getCell(equipmentNameIndex).getStringCellValue();
                    value = value.substring(7);
                    equipmentNameList.add(value);
                } else {
                    equipmentNameList.add("空");
                }

                if (!row.getCell(holderIndex).getCellType().toString().equalsIgnoreCase("BLANK")) {
                    holderList.add(row.getCell(holderIndex).getStringCellValue());
                } else {
                    holderList.add("空");
                }
            }
        }
        excel.close();
        is.close();
        List list = new ArrayList();
        list.add(timeList);
        list.add(equipmentTemperatureList);
        list.add(environmentTemperatureList);
        list.add(environmentHumidityList);
        list.add(windSpeedList);
        list.add(windDirectionList);
        list.add(equipmentNameList);
        list.add(holderList);

        list.add(getEquipmentInformation(sheet.getRow(2)));
        System.out.println("count=>" + count);
        return list;
    }

    public static String getEquipmentInformation(Row row) {
        String value = "";
        List equipmentInformation = new ArrayList();
        if (!row.getCell(0).getCellType().toString().equalsIgnoreCase("BLANK")) {
            //equipmentInformation.add(row.getCell(0).getStringCellValue());//识别类型
            value += row.getCell(0).getStringCellValue();
        }
        if (!row.getCell(6).getCellType().toString().equalsIgnoreCase("BLANK")) {
            //equipmentInformation.add(row.getCell(6).getStringCellValue());//电压等级
            value += " " + row.getCell(6).getStringCellValue();
        }
        if (!row.getCell(7).getCellType().toString().equalsIgnoreCase("BLANK")) {
            //equipmentInformation.add(row.getCell(7).getStringCellValue());//间隔单元
            value += " " + row.getCell(7).getStringCellValue();
        }
        if (!row.getCell(14).getCellType().toString().equalsIgnoreCase("BLANK")) {
            //equipmentInformation.add(row.getCell(14).getStringCellValue());//气压
            value += " 气压:" + row.getCell(14).getStringCellValue();
        }
        if (!row.getCell(15).getCellType().toString().equalsIgnoreCase("BLANK")) {
            //equipmentInformation.add(row.getCell(15).getStringCellValue());//检测距离
            value += " 检测距离:" + row.getCell(15).getStringCellValue();
        }
        /*for(Object o:equipmentInformation){
            value+=o.toString()+" ";
            System.out.println(o);
        }*/
        System.out.println(value);
        return value;
    }

    public static int getRow(@Nullable String path, String dataType) throws IOException {
        File file = new File(path);
        FileInputStream is = new FileInputStream(file);
        XSSFWorkbook excel = new XSSFWorkbook(is);
        XSSFSheet sheet = excel.getSheetAt(0);
        Row row = sheet.getRow(0);
        Iterator cellIterator = row.cellIterator();
        XSSFCell cell;
        String type;
        String value;
        int index = -1;
        while (cellIterator.hasNext()) {
            cell = (XSSFCell) cellIterator.next();
            type = cell.getCellType().toString();
            if (!type.equalsIgnoreCase("BLANK")) {
                value = cell.getStringCellValue();
                //System.out.print(value + "  ");
                index++;
                if (dataType.equals(value)) {
                    break;
                }
            }
        }
        //System.out.println(row.getCell(index));
        return index;
    }

    //@Test
    public void getLastRowNum() throws IOException {
        String path = "D:\\Program\\MyJava\\MyIdea\\PowerTransformerFault\\src\\main\\resources\\static\\data\\one.xlsx";
        File file = new File(path);
        FileInputStream is = new FileInputStream(file);
        XSSFWorkbook excel = new XSSFWorkbook(is);
        XSSFSheet sheet = excel.getSheetAt(0);
        System.out.println(sheet.getLastRowNum());
        System.out.println(excel.getActiveSheetIndex());
        Iterator rowIterator = sheet.rowIterator();
        next(rowIterator, 8);
        long start = System.currentTimeMillis();
        Row row;
        String realPath;
        int count = 8;
        while (rowIterator.hasNext()) {
            row = (Row) rowIterator.next();
            realPath = getPath(row);
            putData(row, realPath);
            System.out.println(count);
            count++;

        }
        System.out.println(System.currentTimeMillis() - start);
        //putData(sheet.getRow(11));
    }

    public String getPath(Row row) {
        String basePath = "D:\\download\\data\\1";
        String path = basePath;

        String date;
        String day;
        //String time;
        String[] year_month_day;
        if (!row.getCell(5).getCellType().toString().equals("BLANK")) {
            date = row.getCell(5).getStringCellValue();
            System.out.println(date);
            day = date.substring(0, date.indexOf(" "));
            year_month_day = day.split("/");
            //time = timeValue.substring(timeValue.indexOf(" ") + 1);
            for (String s : year_month_day) {
                path += "\\" + s;
            }
        }

        File pack = new File(path);
        if (!pack.exists()) {
            pack.mkdirs();
        }
        if (!row.getCell(1).getCellType().toString().equals("BLANK")) {
            String equipmentName = row.getCell(1).getStringCellValue();
            System.out.println(equipmentName);
            path += "\\" + equipmentName.substring(equipmentName.lastIndexOf(" ") + 1) + ".xlsx";
            //System.out.println(equipmentName.substring(0, equipmentName.lastIndexOf("号") + 1));
            //System.out.println(equipmentName.substring(equipmentName.lastIndexOf("变") + 1, equipmentName.lastIndexOf("KV") + 2));
            //System.out.println(equipmentName.substring(equipmentName.lastIndexOf("相") - 1));
        }

        System.out.println("path=>" + path);
        return path;
    }

    public void putData(Row row, String path) throws IOException {

        File file = new File(path);
        if (!file.exists()) {
            FileOutputStream fos = new FileOutputStream(file);
            XSSFWorkbook excel = new XSSFWorkbook();
            XSSFSheet sheet = excel.createSheet("0");
            Row head = sheet.createRow(0);
            putHead(head);
            Row newRow = sheet.createRow(1);
            copyRow(row, newRow);
            excel.write(fos);
            excel.close();
            fos.close();
        } else {
            FileInputStream fis = new FileInputStream(file);
            XSSFWorkbook excel = new XSSFWorkbook(fis);
            XSSFSheet sheet = excel.getSheet("0");
            int lastNum = sheet.getLastRowNum();
            System.out.println(lastNum);
            Row newRow = sheet.createRow(lastNum + 1);
            copyRow(row, newRow);

            FileOutputStream fos = new FileOutputStream(file);
            excel.write(fos);

            fos.close();
            excel.close();
            fis.close();
        }
    }

    public void putHead(Row head) {
        head.createCell(0).setCellValue("识别类型");
        head.createCell(1).setCellValue("设备名称");
        head.createCell(2).setCellValue("设备温度");
        head.createCell(3).setCellValue("报警等级");
        head.createCell(4).setCellValue("判据信息");
        head.createCell(5).setCellValue("检测时间");
        head.createCell(6).setCellValue("电压等级");
        head.createCell(7).setCellValue("间隔单元");
        head.createCell(8).setCellValue("设备类型");
        head.createCell(9).setCellValue("相别");
        head.createCell(10).setCellValue("环境温度");
        head.createCell(11).setCellValue("环境湿度");
        head.createCell(12).setCellValue("风速(m/s)");
        head.createCell(13).setCellValue("风向");
        head.createCell(14).setCellValue("气压(mb)");
        head.createCell(15).setCellValue("检测距离");
        head.createCell(16).setCellValue("负载电流");
        head.createCell(17).setCellValue("云台方位");
        head.createCell(18).setCellValue("机号");
    }

    //@Test
    public static void getSplit() throws IOException {

        String path = "D:\\Program\\MyJava\\MyIdea\\PowerTransformerFault\\src\\main\\resources\\static\\data\\one.xlsx";
        File file = new File(path);
        FileInputStream is = new FileInputStream(file);
        XSSFWorkbook excel = new XSSFWorkbook(is);
        XSSFSheet sheet = excel.getSheetAt(0);
        System.out.println(sheet.getLastRowNum());
        int timeIndex = getRow(path, "检测时间");
        Iterator rowIterator = sheet.iterator();
        //前六行不需要
        next(rowIterator, 6);
        XSSFRow row;
        String timeValue;
        String day;
        String time;

        int month;
        String basePath = "F:\\dataset\\CSV\\data";
        File file2 = new File(basePath);
        FileOutputStream fos = new FileOutputStream(file2);
        XSSFWorkbook excel2 = new XSSFWorkbook();
        excel2.createSheet("2021-8");
        XSSFSheet sheet2 = excel2.getSheetAt(0);
        int index = 0;
        XSSFRow row2 = sheet2.createRow(index++);
        row = (XSSFRow) rowIterator.next();
        copyRow(row, row2);
        printRow(row2);

        while (rowIterator.hasNext()) {
            row = (XSSFRow) rowIterator.next();
            XSSFCell timeCell = row.getCell(timeIndex);
            timeValue = timeCell.getStringCellValue();
            month = Integer.parseInt(String.valueOf(timeValue.charAt(5)));
            if (month == 8) {
                sheet2.createRow(1);
                row2 = sheet2.createRow(index++);
                copyRow(row, row2);
                System.out.println(index);
            }
        }
        excel2.write(fos);
        excel2.close();
        fos.close();
        excel.close();
        is.close();
    }

    public static void next(Iterator iterator, int num) {
        for (int i = 0; i < num; i++) {
            iterator.next();
        }
    }

    public static void copyRow(Row from, Row to) {
        int index = 0;
        Iterator cellIterator = from.cellIterator();
        Cell cell;
        while (cellIterator.hasNext()) {
            cell = (Cell) cellIterator.next();
            if (cell.getCellStyle().toString().equals("BLANK")) {
                to.createCell(index++).setCellValue("0");
            } else {
                to.createCell(index++).setCellValue(cell.getStringCellValue());
            }
            //System.out.println(cell.getStringCellValue()+"  "+to.getCell(index-1));
        }
    }

    public static void printRow(Row from) {
        Iterator cellIterator = from.cellIterator();
        Cell cell;
        while (cellIterator.hasNext()) {
            cell = (Cell) cellIterator.next();
            System.out.println(cell);
        }
    }

    public static String turnDate(String timeValue) {
        //String timeValue = "2021-08-01";
        String[] times = timeValue.split("-");
        if (String.valueOf(times[1].charAt(0)).equals("0")) {
            times[1] = String.valueOf(times[1].charAt(1));
        }
        if (String.valueOf(times[2].charAt(0)).equals("0")) {
            times[2] = String.valueOf(times[2].charAt(1));
        }
        timeValue = times[0] + "/" + times[1] + "/" + times[2];
        return timeValue;
    }

    public static boolean after(String date) {
        //SimpleDateFormat format=new SimpleDateFormat("YYYY-MM-dd");
        //String nowDate=new SimpleDateFormat("YYYY-MM-dd").format(new Date());
        String[] time = date.split("/");
        String path = "D:\\download\\data";
        for (String s : time) {
            path += "\\" + s;
        }
        File file = new File(path);
        if (!file.exists()) {
            return false;
        }
        return true;
    }

    //@Test
    public void getInput() throws Exception {
        File file = new File("D:\\Program\\MyJava\\MyIdea\\PowerTransformerFault\\src\\main\\resources\\static\\h5\\test_input1.xlsx");
        InputStream inputStream = new FileInputStream(file);
        XSSFWorkbook excel = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = excel.getSheetAt(0);
        System.out.println(sheet.getLastRowNum());
    }

    //@Test
    public static List getPre(String path, int count) throws Exception {
        List list = new ArrayList();
        InputStream inputStream = XlsxUtil.class.getClassLoader().getResourceAsStream(path);
        XSSFWorkbook excel = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = excel.getSheetAt(0);
        Row row;

        if (count >= sheet.getLastRowNum()) {
            return list;
        }

        for (int i = 1; i <= 24; i++) {
            row = sheet.getRow(i + count);
            list.add(row.getCell(1).getNumericCellValue());
        }
        excel.close();
        inputStream.close();
        return list;
    }

    /**
     * @param path  读取的excel表格路径
     * @param count 前端传递，从1开始，每次加1
     * @return
     * @throws Exception
     */
    public static List getPre2(String path, int count) throws Exception {
        List list = new ArrayList();
        InputStream inputStream = XlsxUtil.class.getClassLoader().getResourceAsStream(path);
        XSSFWorkbook excel = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = excel.getSheetAt(0);
        Row row;
        if (count >= sheet.getLastRowNum()) {
            return list;
        }
        for (int i = 0; i < 24; i++) {
            row = sheet.getRow(i + count);
            list.add(row.getCell(0).getNumericCellValue());
        }
        excel.close();
        inputStream.close();
        return list;
    }

    public static List getTemperatureFromExcel(File file) {
        List<Map<String, String>> list = new ArrayList<>();
        try {
            Workbook rwb = Workbook.getWorkbook(file);
            Sheet rs = rwb.getSheet(0);//表
            int clos = rs.getColumns();//得到所有的列
            int rows = rs.getRows();//得到所有的行

            System.out.println("表的列数：" + clos + " 表的行数:" + rows);
            for (int i = 1; i < rows; i++) {
                Map<String, String> row = new HashMap<>();
                for (int j = 0; j < clos; j++) {
                    //第一个是列数，第二个是行数
                    // 把列名和列数据放入map中
                    row.put(rs.getCell(j++, 0).getContents(), rs.getCell(j++, i).getContents());
                }
                list.add(row);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    public static List getTemperatureFromExcel(String file) throws IOException {
        return getTemperatureFromExcel(new File(file));
    }
}
